bash
#!/bin/bash

# 设置Hive环境变量
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive





# 创建数据库
hive -e "
CREATE DATABASE edu_ods;
CREATE DATABASE edu_dwd;
CREATE DATABASE edu_dwb;
CREATE DATABASE edu_dws;
CREATE DATABASE edu_dm;
CREATE DATABASE edu_rpt;
"

# 创建表
hive -e "
/* ==建表语句== */
/* 日历表 */
TRUNCATE TABLE edu_ods.calendar;
DROP TABLE IF EXISTS edu_ods.calendar;
CREATE TABLE edu_ods.calendar(
    id       INT   COMMENT '主键',
    datelist STRING
)
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress'='ZLIB')
;

/* 在读学员人数信息表 */
TRUNCATE TABLE edu_ods.class_studying_student_count;
DROP TABLE IF EXISTS edu_ods.class_studying_student_count;
CREATE TABLE edu_ods.class_studying_student_count(
    id                     INT ,
    school_id              INT COMMENT '校区id',
    subject_id             INT COMMENT '学科id',
    class_id               INT COMMENT '班级id',
    studying_student_count INT COMMENT '在读班级人数',
    studying_date          STRING COMMENT '在读日期'
)
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress'='ZLIB')
;

/* 班级排课信息表 */
TRUNCATE TABLE edu_ods.course_table_upload_detail;
DROP TABLE IF EXISTS edu_ods.course_table_upload_detail;
CREATE TABLE edu_ods.course_table_upload_detail(
    id                  INT COMMENT 'id',
    base_id             INT COMMENT '课程主表id',
    class_id            INT COMMENT '班级id',
    class_date          STRING COMMENT '上课日期',
    content             STRING COMMENT '课程内容',
    teacher_id          INT COMMENT '老师id',
    teacher_name        STRING COMMENT '老师名字',
    job_number          STRING COMMENT '工号',
    classroom_id        INT COMMENT '教室id',
    classroom_name      STRING COMMENT '教室名称',
    is_outline          INT COMMENT '是否大纲 0 否 1 是',
    class_mode          INT COMMENT '上课模式 0 传统全天 1 AB上午 2 AB下午 3 线上直播',
    is_stage_exam       INT COMMENT '是否阶段考试（0：否 1：是）',
    is_pay              INT COMMENT '代课费（0：无 1：有）',
    tutor_teacher_id    INT COMMENT '晚自习辅导老师id',
    tutor_teacher_name  STRING COMMENT '辅导老师姓名',
    tutor_job_number    STRING COMMENT '晚自习辅导老师工号',
    is_subsidy          INT COMMENT '晚自习补贴（0：无 1：有）',
    answer_teacher_id   INT COMMENT '答疑老师id',
    answer_teacher_name STRING COMMENT '答疑老师姓名',
    answer_job_number   STRING COMMENT '答疑老师工号',
    remark              STRING COMMENT '备注',
    create_time         STRING COMMENT '创建时间'
)
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress'='ZLIB')
;

/* 学生请假表 */
TRUNCATE TABLE edu_ods.student_leave_apply;
DROP TABLE IF EXISTS edu_ods.student_leave_apply;
CREATE TABLE edu_ods.student_leave_apply(
    id              INT,
    class_id        INT COMMENT '班级id',
    student_id      INT COMMENT '学员id',
    audit_state     INT COMMENT '审核状态 0 待审核 1 通过 2 不通过',
    audit_person    INT COMMENT '审核人',
    audit_time      STRING COMMENT '审核时间',
    audit_remark    STRING COMMENT '审核备注',
    leave_type      INT COMMENT '请假类型  1 请假 2 销假',
    leave_reason    INT COMMENT '请假原因  1 事假 2 病假',
    begin_time      STRING COMMENT '请假开始时间',
    begin_time_type INT COMMENT '1：上午 2：下午',
    end_time        STRING COMMENT '请假结束时间',
    end_time_type   INT COMMENT '1：上午 2：下午',
    days            INT COMMENT '请假/已休天数',
    cancel_state    INT COMMENT '撤销状态  0 未撤销 1 已撤销',
    cancel_time     STRING COMMENT '撤销时间',
    old_leave_id    INT COMMENT '原请假id，只有leave_type =2 销假的时候才有',
    leave_remark    STRING COMMENT '请假/销假说明',
    valid_state     INT COMMENT '是否有效（0：无效 1：有效）',
    create_time     STRING COMMENT '创建时间'
)
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress'='ZLIB')
;

/* 班级作息时间表 */
TRUNCATE TABLE edu_ods.tbh_class_time_table;
DROP TABLE IF EXISTS edu_ods.tbh_class_time_table;
CREATE TABLE edu_ods.tbh_class_time_table(
    id                    INT COMMENT '主键id',
    class_id              INT COMMENT '班级id',
    morning_template_id   INT COMMENT '上午出勤模板id',
    morning_begin_time    STRING COMMENT '上午开始时间',
    morning_end_time      STRING COMMENT '上午结束时间',
    afternoon_template_id INT COMMENT '下午出勤模板id',
    afternoon_begin_time  STRING COMMENT '下午开始时间',
    afternoon_end_time    STRING COMMENT '下午结束时间',
    evening_template_id   INT COMMENT '晚上出勤模板id',
    evening_begin_time    STRING COMMENT '晚上开始时间',
    evening_end_time      STRING COMMENT '晚上结束时间',
    use_begin_date        STRING COMMENT '使用开始日期',
    use_end_date          STRING COMMENT '使用结束日期',
    create_time           STRING COMMENT '创建时间',
    create_person         INT COMMENT '创建人',
    remark                STRING COMMENT '备注'
)
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress'='ZLIB')
;

/* 学员签到信息表 */
TRUNCATE TABLE edu_ods.tbh_student_signin_record;
DROP TABLE IF EXISTS edu_ods.tbh_student_signin_record;
CREATE TABLE edu_ods.tbh_student_signin_record(
    id                INT COMMENT '主键id',
    normal_class_flag INT COMMENT '是否正课 1 正课 2 自习',
    time_table_id     INT COMMENT '作息时间id 关联tbh_school_time_table 或者 tbh_class_time_table',
    class_id          INT COMMENT '班级id',
    student_id        INT COMMENT '学员id',
    signin_time       STRING COMMENT '签到时间',
    signin_date       STRING COMMENT '签到日期',
    inner_flag        INT COMMENT '内外网标志  0 外网 1 内网',
    signin_type       INT COMMENT '签到类型 1 心跳打卡 2 老师补卡',
    share_state       INT COMMENT '共享屏幕状态 0 否 1是  在上午或下午段有共屏记录，则该段所有记录该字段为1，内网默认为1 外网默认为0 ',
    inner_ip          STRING COMMENT '内网ip地址'
)
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress'='ZLIB')
;
"

/* ==sqoop全量采集== */
DT=`date -d '-1 day' +%Y-%m-%d`

/*日历表*/
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/teach?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${DT}' as dt from calendar where 1=1 and  \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table calendar \
-m 1

/* 在读学员人数信息表 */
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/teach?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${DT}' as dt from class_studying_student_count where 1=1 and  \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table class_studying_student_count \
-m 1

/* 班级排课信息表 */
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/teach?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${DT}' as dt from course_table_upload_detail where 1=1 and  \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table course_table_upload_detail \
-m 1

/* 学生请假表 */
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/teach?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${DT}' as dt from student_leave_apply where 1=1 and (create_time between '2010-01-01 00:00:00' and '${DT} 23:59:59') and  \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table student_leave_apply \
-m 1

/* 班级作息时间表 */
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/teach?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${DT}' as dt from tbh_class_time_table where 1=1 and (create_time between '2010-01-01 00:00:00' and '${DT} 23:59:59') and  \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table tbh_class_time_table \
-m 1

/* 学员签到信息表 */
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/teach?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${DT}' as dt from tbh_student_signin_record where signin_time between '2010-01-01 00:00:00' and '${DT} 23:59:59' and  \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table tbh_student_signin_record \
-m 1

-----------------------------------------------------------------------------------
/* ==sqoop增量采集== */
DT=`date -d '-1 day' +%Y-%m-%d`

/*日历表*/
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/teach?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${DT}' as dt from calendar where 1=1 and  \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table calendar \
-m 1

/* 在读学员人数信息表 */
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/teach?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${DT}' as dt from class_studying_student_count where 1=1 and  \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table class_studying_student_count \
-m 1

/* 班级排课信息表 */
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/teach?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${DT}' as dt from course_table_upload_detail where 1=1 and  \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table course_table_upload_detail \
-m 1

/* 学生请假表 */
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/teach?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${DT}' as dt from student_leave_apply where 1=1 and (create_time between '${DT}  00:00:00' and '${DT} 23:59:59') and  \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table student_leave_apply \
-m 1

/* 班级作息时间表 */
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/teach?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${DT}' as dt from tbh_class_time_table where 1=1 and (create_time between '${DT}  00:00:00' and '${DT} 23:59:59') and  \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table tbh_class_time_table \
-m 1

/* 学员签到信息表 */
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/teach?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${DT}' as dt from tbh_student_signin_record where signin_time between '${DT}  00:00:00' and '${DT} 23:59:59' and  \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table tbh_student_signin_record \
-m 1

